/**
 * 
 */
package br.embedded.socialapp.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;

import br.embedded.socialapp.domain.GraphBuilder;
import br.embedded.socialapp.domain.PersonName;
import br.embedded.socialapp.domain.User;

/**
 * @author daniel
 *
 */
public class FriendshipDAO
{
	private static final String insertSql = "insert into friendship (user_login1, user_login2, social_network) values (?, ?, ?);";
	
	private static final String query = "select t1.user_login1, t2.first_name as first_name1, t2.last_name as last_name1, "
										+ "t1.user_login2, t3.first_name as first_name2, t3.last_name as last_name2, t1.social_network from friendship t1 "
										+ "inner join cadastro_usuario t2 on (t1.user_login1 = t2.user_login) "
										+ "inner join cadastro_usuario t3 on (t1.user_login2 = t3.user_login);";
	
	
	private static FriendshipDAO singleton;
	
	private FriendshipDAO()
	{
		ConfigurationReader.readAllParameters();
	}
	
	public static FriendshipDAO getInstance()
	{
		if (singleton == null)
			singleton = new FriendshipDAO();
		return singleton;
	}
	
	public synchronized void create(String userLogin1, String userLogin2, int networkId)
	{
		Connection connection = null;
		PreparedStatement statement = null;
		
		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			statement = connection.prepareStatement(insertSql);
			
			statement.setString(1, userLogin1);
			statement.setString(2, userLogin2);
			statement.setInt(3, networkId);
			statement.execute();			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}
	}
	
	public synchronized List<GraphBuilder> getUsers()
	{
		List<GraphBuilder> lists = new ArrayList<GraphBuilder>(3);
		
		GraphBuilder orkutGraph = new GraphBuilder("Orkut");
		GraphBuilder myspaceGraph = new GraphBuilder("Myspace");
		GraphBuilder intraNetsGraph = new GraphBuilder("Merged");
		
		Connection connection = null;
		Statement statement = null;
		
		Hashtable<String, List<String>> hash = CadastroUsuarioDAO.getInstance().getUsersId();
		
		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			statement = connection.createStatement();
			
			ResultSet resultSet = statement.executeQuery(query);
			
			while (resultSet.next())
			{
				User user1 = readUser(resultSet.getString("user_login1"), resultSet.getString("first_name1"),
									  resultSet.getString("last_name1"));
				
				User user2 = readUser(resultSet.getString("user_login2"), resultSet.getString("first_name2"),
						  			  resultSet.getString("last_name2"));
				
				if (resultSet.getInt("social_network") == 1)
				{
					if (hash.containsKey(user1.getLogin()))
						user1.setId(hash.get(user1.getLogin()).get(0));
					if (hash.containsKey(user2.getLogin()))
						user2.setId(hash.get(user2.getLogin()).get(0));
					
					orkutGraph.add(user1, user2);
					intraNetsGraph.add(user1, user2, orkutGraph.getNetwork());
				}
				else
				{
					if (hash.containsKey(user1.getLogin()))
						user1.setId(hash.get(user1.getLogin()).get(1));
					if (hash.containsKey(user2.getLogin()))
						user2.setId(hash.get(user2.getLogin()).get(1));
					
					myspaceGraph.add(user1, user2);
					intraNetsGraph.add(user1, user2, myspaceGraph.getNetwork());
				}
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}
		lists.add(0, orkutGraph);
		lists.add(1, myspaceGraph);
		lists.add(2, intraNetsGraph);
		
		return lists;
	}
	
	private User readUser(String login, String firstName, String lastName)
	{
		User user = new User();
		user.setLogin(login);
		PersonName name = new PersonName();
		name.setGivenName(firstName);
		name.setFamilyName(lastName);
		user.setPname(name);
		
		return user;
	}
}
